CREATE TABLE [dbo].[tb_SubOrganizaciones]
(
[idSubOrganizacion] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Org] [int] NOT NULL,
[EsPropia] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Grilla] [int] NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaBaja] [datetime] NULL,
[Domicilio] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Localidad] [int] NOT NULL,
[CorreoElectronico] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Nivel] [int] NOT NULL,
[Modalidad] [int] NULL,
[Zona] [int] NOT NULL,
[CUE] [int] NULL,
[Telefono] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Jornada] [int] NULL,
[CantTurnosFunc] [int] NULL,
[Categoria] [int] NULL,
[EsPrivada] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Tipo] [int] NULL,
[Mnemo] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrganizacionLiquidadora] [int] NULL,
[Chequeada] [int] NULL,
[Ambito] [int] NULL,
[organismo_id_sarrhh] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_SubOrganizaciones] ON [dbo].[tb_SubOrganizaciones]
FOR UPDATE AS

declare @c int

/*
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_SubOrganizaciones set FechaBaja = getdate() where idSubOrganizacion=(Select idSubOrganizacion from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_SubOrganizaciones set FechaBaja= null where idSubOrganizacion=(Select idSubOrganizacion from inserted)

*/
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updCUPOFfromSubOrganizacion] ON [dbo].[tb_SubOrganizaciones]
FOR UPDATE
AS

Declare @IMnemo Varchar(4), @DMnemo Varchar(4), @idSubOrganizacion Int, @idPlaza Int
Declare SubOrganizacion Cursor For 
Select i.Mnemo, d.Mnemo, i.idSubOrganizacion From deleted d, inserted i Where i.idSubOrganizacion = d.idSubOrganizacion

Open SubOrganizacion
Fetch Next From SubOrganizacion Into @IMnemo, @DMnemo, @idSubOrganizacion

While @@Fetch_Status = 0
Begin
	If @DMnemo <> @IMnemo
	Begin
		Declare plazas Cursor For 
		Select idPlaza From tb_Plazas Where SubOrganizacion = @idSubOrganizacion
		Open Plazas
		Fetch Next From Plazas Into @idPlaza
		While @@Fetch_Status = 0
		Begin
			Exec spActualizarCupof @idPlaza
			Fetch Next From Plazas Into @idPlaza
		End
		Close Plazas
		Deallocate Plazas
	End
	Fetch Next From SubOrganizacion Into @IMnemo, @DMnemo, @idSubOrganizacion
End

Close SubOrganizacion
Deallocate SubOrganizacion
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] ADD CONSTRAINT [PK_tb_SubOrganizaciones] PRIMARY KEY CLUSTERED  ([idSubOrganizacion]) WITH (FILLFACTOR=100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxorg] ON [dbo].[tb_SubOrganizaciones] ([idSubOrganizacion], [Org]) WITH (FILLFACTOR=100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_TiposDeDomicilios] FOREIGN KEY ([Ambito]) REFERENCES [dbo].[tb_TiposDeDomicilios] ([idTipoDomicilio])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_CategoriasDeSuborganizaciones] FOREIGN KEY ([Categoria]) REFERENCES [dbo].[tb_CategoriasDeSuborganizaciones] ([IdCategoria])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_Jornadas] FOREIGN KEY ([Jornada]) REFERENCES [dbo].[tb_Jornadas] ([idJornada])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tbSubOrganizaciones_tbJornadas] FOREIGN KEY ([Jornada]) REFERENCES [dbo].[tb_Jornadas] ([idJornada])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_ModalidadesEnsenanza] FOREIGN KEY ([Modalidad]) REFERENCES [dbo].[tb_ModalidadesEnsenanza] ([idModalidad])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_NivelesEnsenanza] FOREIGN KEY ([Nivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tbSubOrganizaciones_tbNivelesEnsenanza] FOREIGN KEY ([Nivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_Organizaciones] FOREIGN KEY ([Org]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tbSubOrganizaciones_tbOrganizaciones] FOREIGN KEY ([Org]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_Organizaciones1] FOREIGN KEY ([OrganizacionLiquidadora]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tbSubOrganizaciones_tbOrganizaciones1] FOREIGN KEY ([OrganizacionLiquidadora]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubOrganizaciones_tb_zonas] FOREIGN KEY ([Zona]) REFERENCES [dbo].[tb_Zonas] ([idZona])
GO
ALTER TABLE [dbo].[tb_SubOrganizaciones] WITH NOCHECK ADD CONSTRAINT [FK_tbSubOrganizaciones_tbzonas] FOREIGN KEY ([Zona]) REFERENCES [dbo].[tb_Zonas] ([idZona])
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_SubOrganizaciones].[EsPropia]'
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_SubOrganizaciones].[Baja]'
GO
GRANT SELECT ON  [dbo].[tb_SubOrganizaciones] TO [Certificaciones]
GRANT SELECT ON  [dbo].[tb_SubOrganizaciones] TO [CertificacionesAdministracion]
GRANT SELECT ON  [dbo].[tb_SubOrganizaciones] TO [pregase]
GO
